﻿using Dapper;
using Microsoft.Extensions.Options;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Threading.Tasks;

namespace Xman.Dapper.Sqlserver
{
    public class SqlContext
    {
        private readonly SqlContextOptions _options;
        public SqlContext(IOptions<SqlContextOptions> options)
        {
            _options = options.Value;
        }

        public async Task<IDbConnection> OpenConnectionAsync()
        {
            var connection = new SqlConnection(_options.Connstr);
            await connection.OpenAsync();
            return connection;
        }

        public async Task<int> ExcuteCommandAsync(string sqlString, object entityParameter = null, IDbTransaction transaction = null, CommandType? commandType = null)
        {
            if (transaction != null)
            {
                return await transaction.Connection.ExecuteAsync(sqlString, entityParameter, transaction, commandType: commandType);
            }
            using var connection = await OpenConnectionAsync();
            return await connection.ExecuteAsync(sqlString, entityParameter, commandType: commandType);
        }

        public async Task<int> ExecuteScalarAsync(string sqlString, object entityParameter = null, IDbTransaction transaction = null, CommandType? commandType = null)
        {
            if (transaction != null)
            {
                return await transaction.Connection.ExecuteScalarAsync<int>(sqlString, entityParameter, transaction, commandType: commandType);
            }
            using var connection = await OpenConnectionAsync();
            return await connection.ExecuteScalarAsync<int>(sqlString, entityParameter, commandType: commandType);
        }

        public async Task<IEnumerable<T>> GetListAsync<T>(string sqlString, object entityParameter = null, IDbTransaction transaction = null, CommandType? commandType = null)
        {
            if (transaction != null)
            {
                return await transaction.Connection.QueryAsync<T>(sqlString, entityParameter, transaction,commandType: commandType);
            }
            using var connection = await OpenConnectionAsync();
            return await connection.QueryAsync<T>(sqlString, entityParameter, commandType: commandType);
        }

        public async Task<T> GetFirstOrDefaultAsync<T>(string sqlString, object entityParameter = null, IDbTransaction transaction = null, CommandType? commandType = null)
        {
            if (transaction != null)
            {
                return await transaction.Connection.QueryFirstOrDefaultAsync<T>(sqlString, entityParameter, transaction, commandType: commandType);
            }
            using var connection = await OpenConnectionAsync();
            return await connection.QueryFirstOrDefaultAsync<T>(sqlString, entityParameter, commandType: commandType);
        }

        public async Task<T> QueryFirstOrDefaultAsync<T>(string sqlString, object entityParameter = null, IDbTransaction transaction = null, CommandType? commandType = null)
        {
            if (transaction != null)
            {
                var result= await transaction.Connection.QueryAsync<T>(sqlString, entityParameter, transaction, commandType: commandType);
                return result.FirstOrDefault();
            }
            using var connection = await OpenConnectionAsync();
            return await connection.QueryFirstOrDefaultAsync<T>(sqlString, entityParameter,commandType:commandType);
        }

        public async Task<IEnumerable<TReturn>> QueryAsync<TFirst, TSecond, TReturn>(string sql, Func<TFirst, TSecond, TReturn> map, object param = null, IDbTransaction transaction = null, bool buffered = true, string splitOn = "Id", int? commandTimeout = null, CommandType? commandType = null)
        {
            if (transaction != null)
            {
                return await transaction.Connection.QueryAsync(sql, map, param, transaction, buffered, splitOn, commandTimeout, commandType);
            }
            using var connection = await OpenConnectionAsync();
            return await connection.QueryAsync(sql, map, param, null, buffered, splitOn, commandTimeout, commandType);
        }

        public async Task<IEnumerable<TReturn>> QueryAsync<TFirst, TSecond, TThird, TReturn>(string sql, Func<TFirst, TSecond, TThird, TReturn> map, object param = null, IDbTransaction transaction = null, bool buffered = true, string splitOn = "Id", int? commandTimeout = null, CommandType? commandType = null)
        {
            if (transaction != null)
            {
                return await transaction.Connection.QueryAsync(sql, map, param, transaction, buffered, splitOn, commandTimeout, commandType);
            }

            using var connection = await OpenConnectionAsync();
            return await connection.QueryAsync(sql, map, param, null, buffered, splitOn, commandTimeout, commandType);
        }
        public async Task<IEnumerable<TReturn>> QueryAsync<TFirst, TSecond, TThird, TFourth, TReturn>(string sql, Func<TFirst, TSecond, TThird, TFourth, TReturn> map, object param = null, IDbTransaction transaction = null, bool buffered = true, string splitOn = "Id", int? commandTimeout = null, CommandType? commandType = null)
        {
            if (transaction != null)
            {
                return await transaction.Connection.QueryAsync(sql, map, param, transaction, buffered, splitOn, commandTimeout, commandType);
            }

            using var connection = await OpenConnectionAsync();
            return await connection.QueryAsync(sql, map, param, null, buffered, splitOn, commandTimeout, commandType);
        }

        public async Task<IEnumerable<TReturn>> QueryAsync<TFirst, TSecond, TThird, TFourth, TFifth, TReturn>(string sql, Func<TFirst, TSecond, TThird, TFourth, TFifth, TReturn> map, object param = null, IDbTransaction transaction = null, bool buffered = true, string splitOn = "Id", int? commandTimeout = null, CommandType? commandType = null)
        {
            if (transaction != null)
            {
                return await transaction.Connection.QueryAsync(sql, map, param, transaction, buffered, splitOn, commandTimeout, commandType);
            }

            using var connection = await OpenConnectionAsync();
            return await connection.QueryAsync(sql, map, param, null, buffered, splitOn, commandTimeout, commandType);
        }

        public async Task<DataTable> GetDataTableAsync(string sql)
        {
            var ds = new DataSet();
            using var connection = await OpenConnectionAsync();
            var cmd = new SqlCommand(sql, (SqlConnection)connection);
            var adapter = new SqlDataAdapter(cmd);
            adapter.Fill(ds);
            return ds.Tables[0];
        }

        public async Task ExcuteTransAsync(List<TranModel> list)
        {
            using var connection = await OpenConnectionAsync();
            await using var tran = (SqlTransaction)await ((SqlConnection)connection).BeginTransactionAsync();
            var method = "";
            var result=0;
            try
            {
                foreach (var t in list)
                {
                    method = t.MethodName;
                    result = await t.Func(tran, result);
                }

                await tran.CommitAsync();
            }
            catch (Exception ex)
            {
                await tran.RollbackAsync();
                throw new Exception($"异常：方法名-{method}，信息：{ex}");
            }
        }

        public async Task BlukInsertAsync<T>(IEnumerable<T> entityList)
        {
            await using var connection = await OpenConnectionAsync() as SqlConnection;
            using var bulkCopy = new SqlBulkCopy(connection);
            bulkCopy.BatchSize = entityList.Count();
            bulkCopy.DestinationTableName = typeof(T).Name;
            var table = new DataTable();
            var props = typeof(T).GetProperties();
            foreach (var propertyInfo in props)
            {
                bulkCopy.ColumnMappings.Add(propertyInfo.Name, propertyInfo.Name);
                table.Columns.Add(propertyInfo.Name, Nullable.GetUnderlyingType(propertyInfo.PropertyType) ?? propertyInfo.PropertyType);
            }
            var values = new object[props.Length];
            foreach (var item in entityList)
            {
                for (var i = 0; i < values.Length; i++)
                {
                    values[i] = props[i].GetValue(item, null);
                }
                table.Rows.Add(values);
            }
            await bulkCopy.WriteToServerAsync(table);
        }

    }
}
